package com.cz.modules.cll.controller;


import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.util.*;

import cn.hutool.core.date.DateUtil;
import com.cz.common.utils.HikvisionApiUtil;
import com.cz.common.validator.ValidatorUtils;
import com.cz.modules.gc.entity.StatoiletseatEntity;
import com.cz.modules.gc.entity.StatoiletstatusEntity;
import com.cz.modules.gc.service.GcToiletService;
import com.cz.modules.gc.service.StatoiletseatService;
import com.cz.modules.gc.service.StatoiletstatusService;
import com.cz.modules.jxc.entity.JxcShopEntity;
import com.cz.modules.jxc.service.JxcSaleOrderService;
import com.cz.modules.jxc.service.JxcShopService;
import com.cz.modules.rll.service.RllHourCountService;
import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import com.cz.modules.cll.entity.CllVehicleRecordEntity;
import com.cz.modules.cll.service.CllVehicleRecordService;
import com.cz.common.utils.PageUtils;
import com.cz.common.utils.R;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


/**
 * 车流量每日信息
 *
 * @author Cz
 * @email 
 * @date 2020-04-23 14:09:10
 */
@RestController
@RequestMapping("cll/cllvehiclerecord")
public class CllVehicleRecordController {
    @Autowired
    private GcToiletService gcToiletService;
    @Autowired
    private CllVehicleRecordService cllVehicleRecordService;
    @Autowired
    private JxcShopService jxcShopService;
    @Autowired
    private RllHourCountService rllHourCountService;
    @Autowired
    private JxcSaleOrderService jxcSaleOrderService;
    @Autowired
    private StatoiletstatusService statoiletstatusService;

    //车流量
    @RequestMapping("/upload")
    public void upload(@RequestParam Map<String, Object> params, HttpServletResponse response){
        String southMonitoringId ="1bd4530bfe534770a11ea196b34663fc";//南区入口id
        String northMonitoringId ="095852b3f7c948d6b27d5fd698bd1c37";//北区入口id
        String type= (String) params.get("type");
        String stime= (String) params.get("startTime");
        String etime= (String) params.get("endTime");
        Date dd=new Date();
        String date=DateUtil.format(dd,"yyyy-MM-dd");
        String startTime="";
        String endTime="";
        if("".equals(type) || "null".equals(type)){
            type="7";
        }
        Calendar calendar = Calendar.getInstance();
        if(!"".equals(stime) &&  !"null".equals(stime) && !"".equals(etime) &&  !"null".equals(etime)){//自定义时间
            startTime =stime+" 00:00:00";
            endTime =etime+" 23:59:59";
        }else{//无自定义时间
            if("7".equals(type)){//近7天
                calendar.setTime(dd);
                calendar.add(Calendar.DAY_OF_MONTH, -6);
                startTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                startTime +=" 00:00:00";
                endTime =date+" 23:59:59";
            }else if("30".equals(type)){//近30天
                calendar.setTime(dd);
                calendar.add(Calendar.DAY_OF_MONTH, -29);
                startTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                startTime +=" 00:00:00";
                endTime =date+" 23:59:59";
            }
        }
        List<JxcShopEntity> shopList=jxcShopService.list();
        List<JxcShopEntity> southShopList=new ArrayList<>();
        List<JxcShopEntity> northShopList=new ArrayList<>();
        for (JxcShopEntity shop:shopList) {
            if(shop.getDeptId().equals("3")){//南区
                southShopList.add(shop);
            }else if(shop.getDeptId().equals("4")){
                northShopList.add(shop);
            }
        }
        List sclllist= cllVehicleRecordService.cllChartday(southMonitoringId,startTime,endTime);//南区车流量
        List nclllist=cllVehicleRecordService.cllChartday(northMonitoringId,startTime,endTime);//北区车流量

        List<String> timeList = (List<String>)sclllist.get(0);
        List<Integer> clls = (List<Integer>)sclllist.get(1);//南区车流
        List<Integer> clln = (List<Integer>)nclllist.get(1);



        String str[]={"日期","南区车流","北区车流","合计"};
        String fileName = "车流量数据统计"+new Date().getTime()+".xls";
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("车流量统计");
        for (int i = 0; i <str.length ; i++) {
            sheet.setColumnWidth(i, (int)20*100);//列宽 excel中1字符约等于 此处2.8
        }
        // sheet.setColumnWidth(0, (int)19.3*100);//列宽 excel中1字符约等于 此处2.8
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));//合并单元格 第一行标题
        HSSFCellStyle style = workbook.createCellStyle();//样式  标题用

        //创建Excel工作表第一行
        HSSFRow row0 = sheet.createRow(0);
        HSSFCell cell0 = row0.createCell(0);

        row0.setHeight((short) 800);//行高 1磅=20short
        cell0.setCellValue(new HSSFRichTextString("阳曲服务区车流量数据统计"));
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short)12);//字体大小
        font.setBold(true);//加粗
        style.setFont(font);//将字体样式放入style
        font.setFontName("宋体");
        style.setAlignment(HorizontalAlignment.CENTER);//左右居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
        cell0.setCellStyle(style);//将style放入cell

        HSSFCellStyle style1 = workbook.createCellStyle();//样式1 第二行用
        HSSFFont font1 = workbook.createFont();
        font1.setFontHeightInPoints((short)12);//字体大小
        font1.setBold(false);//加粗
        font1.setFontName("宋体");
        style1.setFont(font1);//将字体样式放入style
        style1.setAlignment(HorizontalAlignment.CENTER);//左右居中
        style1.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
        style1.setWrapText(true);//自动换行
        style1.setBorderBottom(BorderStyle.THIN); //下边框
        style1.setBorderLeft(BorderStyle.THIN);//左边框
        style1.setBorderTop(BorderStyle.THIN);//上边框
        style1.setBorderRight(BorderStyle.THIN);//右边框


        HSSFCellStyle style2 = workbook.createCellStyle();//样式2 带绿色背景颜色的
        style2.setFont(font1);//将字体样式放入style
        style2.setAlignment(HorizontalAlignment.CENTER);//左右居中
        style2.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
        style2.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex());
        style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style2.setWrapText(true);//自动换行
        style2.setBorderBottom(BorderStyle.THIN); //下边框
        style2.setBorderLeft(BorderStyle.THIN);//左边框
        style2.setBorderTop(BorderStyle.THIN);//上边框
        style2.setBorderRight(BorderStyle.THIN);//右边框

        HSSFCellStyle style3 = workbook.createCellStyle();//样式3 带红色背景颜色的 倒数第二行 合计用
        style3.setFont(font1);//将字体样式放入style
        style3.setAlignment(HorizontalAlignment.CENTER);//左右居中
        style3.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
        style3.setFillForegroundColor(IndexedColors.RED.getIndex());
        style3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style3.setBorderBottom(BorderStyle.THIN); //下边框
        style3.setBorderLeft(BorderStyle.THIN);//左边框
        style3.setBorderTop(BorderStyle.THIN);//上边框
        style3.setBorderRight(BorderStyle.THIN);//右边框

        HSSFCellStyle style4 = workbook.createCellStyle();//样式4 带蓝色背景颜色的 最后一行平均用
        style4.setFont(font1);//将字体样式放入style
        style4.setAlignment(HorizontalAlignment.CENTER);//左右居中
        style4.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
        style4.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());
        style4.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style4.setBorderBottom(BorderStyle.THIN); //下边框
        style4.setBorderLeft(BorderStyle.THIN);//左边框
        style4.setBorderTop(BorderStyle.THIN);//上边框
        style4.setBorderRight(BorderStyle.THIN);//右边框
        //第二行
        HSSFRow row1 = sheet.createRow(1);
        for (int i = 0; i <str.length ; i++) {
            HSSFCell cell1 = row1.createCell(i);
            row1.setHeight((short) 900); // 设置行高
            cell1.setCellValue(new HSSFRichTextString(str[i]));
            if(i == 3 || i == 6 || i == 9 || i == 12 || i == 15 || i==18 || i==21){
                cell1.setCellStyle(style2);//将style放入cell
            }else {
                cell1.setCellStyle(style1);//将style放入cell
            }
        }
        int totalcll=0;
        int totalclls=0;
        int totalclln=0;


        DecimalFormat df = new DecimalFormat("0.00");
        DecimalFormat df1 = new DecimalFormat("0");
        //创建内容所有行
        for (int i = 0; i <timeList.size() ; i++) {
            HSSFRow row= sheet.createRow(i+2);//创建行
            row.setHeight((short) 600); // 设置行高
            for (int j = 0; j <str.length ; j++) {
                HSSFCell cell = row.createCell(j);//创建单元格
                if(j ==0){//日期
                    cell.setCellValue(timeList.get(i));
                }else if(j ==1){//车流
                    totalclls +=clls.get(i);
                    cell.setCellValue(clls.get(i));
                }else if(j ==2){
                    totalclln +=clln.get(i);
                    cell.setCellValue(clln.get(i));
                }else if(j ==3){
                    totalcll +=((clls.get(i) + clln.get(i)));
                    cell.setCellValue((clls.get(i) + clln.get(i)));
                }
                cell.setCellStyle(style1);
            }
        }
        //总计
        HSSFRow rowLast2= sheet.createRow(timeList.size()+2);//创建行
        rowLast2.setHeight((short) 600); // 设置行高
        for (int i = 0; i <str.length ; i++) {
            HSSFCell cell = rowLast2.createCell(i);//创建单元格
            if(i == 0){
                cell.setCellValue(new HSSFRichTextString("合计"));
            }else if(i == 1){
                cell.setCellValue(totalclls);
            }else if(i == 2){
                cell.setCellValue(totalclln);
            }else if(i == 3){
                cell.setCellValue(totalcll);
            }
            cell.setCellStyle(style3);
        }
        //平均
        HSSFRow rowLast= sheet.createRow(timeList.size()+3);//创建行
        rowLast.setHeight((short) 600); // 设置行高
        int size=timeList.size();
        if(size ==0){
            size =1;
        }
        for (int i = 0; i <str.length ; i++){
            HSSFCell cell = rowLast.createCell(i);//创建单元格
            if(i == 0){
                cell.setCellValue("平均");
            }else if(i == 1){
                String kgratio = df1.format((float)totalclls/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(dzg);
            }else if(i == 2){
                String kgratio = df1.format((float)totalclln/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(dzg);
            }else if(i == 3){
                String kgratio = df1.format((float)totalcll/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(dzg);
            }
            cell.setCellStyle(style4);
        }
        try {
            fileName = new String(fileName.getBytes(),"ISO8859-1");
            OutputStream output=response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename="+fileName);
            response.setContentType("application/msexcel");
            workbook.write(output);
            output.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 导出
     */
    @RequestMapping("/exportcll")
    public void exportcll(@RequestParam Map<String, Object> params, HttpServletResponse response){
        String southMonitoringId ="1bd4530bfe534770a11ea196b34663fc";//南区入口id
        String northMonitoringId ="095852b3f7c948d6b27d5fd698bd1c37";//北区入口id
        String type= (String) params.get("type");
        String stime= (String) params.get("startTime");
        String etime= (String) params.get("endTime");
        Date dd=new Date();
        String date=DateUtil.format(dd,"yyyy-MM-dd");
        String startTime="";
        String endTime="";
        if("".equals(type) || "null".equals(type)){
            type="7";
        }
        Calendar calendar = Calendar.getInstance();
        if(!"".equals(stime) &&  !"null".equals(stime) && !"".equals(etime) &&  !"null".equals(etime)){//自定义时间
            startTime =stime+" 00:00:00";
            endTime =etime+" 23:59:59";
        }else{//无自定义时间
            if("7".equals(type)){//近7天
                calendar.setTime(dd);
                calendar.add(Calendar.DAY_OF_MONTH, -6);
                startTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                startTime +=" 00:00:00";
                endTime =date+" 23:59:59";
            }else if("30".equals(type)){//近30天
                calendar.setTime(dd);
                calendar.add(Calendar.DAY_OF_MONTH, -29);
                startTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                startTime +=" 00:00:00";
                endTime =date+" 23:59:59";
            }
        }
        List<JxcShopEntity> shopList=jxcShopService.list();
        List<JxcShopEntity> southShopList=new ArrayList<>();
        List<JxcShopEntity> northShopList=new ArrayList<>();
        for (JxcShopEntity shop:shopList) {
            if(shop.getDeptId().equals("3")){//南区
                southShopList.add(shop);
            }else if(shop.getDeptId().equals("4")){
                northShopList.add(shop);
            }
        }
        List sclllist= cllVehicleRecordService.cllChartday(southMonitoringId,startTime,endTime);//南区车流量
        List nclllist=cllVehicleRecordService.cllChartday(northMonitoringId,startTime,endTime);//北区车流量

        List srlllist=rllHourCountService.kllChartday("南区",startTime,endTime);//南区客流量 list.get(1)
        List nrlllist=rllHourCountService.kllChartday("北区",startTime,endTime);//北区客流量

        List skdj=jxcSaleOrderService.southAndNorthAveragePriceDay(southShopList,startTime,endTime);//南区客单价 list.get(1)
        List nkdj=jxcSaleOrderService.southAndNorthAveragePriceDay(northShopList,startTime,endTime);//北区客单价

        List srjxf=jxcSaleOrderService.proportionOfConsumers("3",startTime,endTime);//南区人均消费占比
        List nrjxf=jxcSaleOrderService.proportionOfConsumers("4",startTime,endTime);//北区人均消费占比

        List southOrderSize=jxcSaleOrderService.orderSizelist(southShopList,startTime,endTime );//南区订单数
        List northOrderSize=jxcSaleOrderService.orderSizelist(northShopList,startTime,endTime); //北区订单数

        List sSaleCount= jxcSaleOrderService.orderCountlist(southShopList,startTime,endTime);//南区销售额
        List nSaleCount=jxcSaleOrderService.orderCountlist(northShopList,startTime,endTime);//北区销售额


        /*List sgc= gcToiletService.gcCount("1",startTime,endTime);
        //北区厕位
        List ngc=  gcToiletService.gcCount("0",startTime,endTime);*/

        List<Integer> maleSouth=statoiletstatusService.getCount("003",startTime,endTime);
        List<Integer> femaleSouth=statoiletstatusService.getCount("004",startTime,endTime);
        List<Integer> maleNorth=statoiletstatusService.getCount("001",startTime,endTime);
        List<Integer> femaleNorth=statoiletstatusService.getCount("002",startTime,endTime);


        List<String> timeList = (List<String>)sclllist.get(0);
        List<Integer> clls = (List<Integer>)sclllist.get(1);//南区车流
        List<Integer> clln = (List<Integer>)nclllist.get(1);
        List<Integer> rlls= (List<Integer>) srlllist.get(1);//南区客流
        List<Integer> rlln= (List<Integer>) nrlllist.get(1);
        List<Double> southkdj= (List<Double>) skdj.get(1);//南区客单价
        List<Double> northkdj= (List<Double>) nkdj.get(1);


        String str[]={"日期","南区车流","北区车流","合计","南区客流","北区客流","合计","南区卫生间使用人次","北区卫生间使用人次","两区卫生间使用人次","南区销售额","北区销售额","总销售额","南区订单量","北区订单量","总订单量",
                "南区客单价","北区客单价","总客单价","南区消费人次占比 %","北区消费人次占比 %","两区消费人次占比 %",};
        String fileName = "数据统计"+new Date().getTime()+".xls";
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("数据统计");
        for (int i = 0; i <str.length ; i++) {
            sheet.setColumnWidth(i, (int)40*100);//列宽 excel中1字符约等于 此处2.8
        }
       // sheet.setColumnWidth(0, (int)19.3*100);//列宽 excel中1字符约等于 此处2.8
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,21));//合并单元格 第一行标题
        HSSFCellStyle style = workbook.createCellStyle();//样式  标题用

        //创建Excel工作表第一行
        HSSFRow row0 = sheet.createRow(0);
        HSSFCell cell0 = row0.createCell(0);

        row0.setHeight((short) 800);//行高 1磅=20short
        cell0.setCellValue(new HSSFRichTextString("阳曲服务区数据统计"));
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short)22);//字体大小
        font.setBold(true);//加粗
        style.setFont(font);//将字体样式放入style
        font.setFontName("宋体");
        style.setAlignment(HorizontalAlignment.CENTER);//左右居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
        cell0.setCellStyle(style);//将style放入cell

        HSSFCellStyle style1 = workbook.createCellStyle();//样式1 第二行用
        HSSFFont font1 = workbook.createFont();
        font1.setFontHeightInPoints((short)12);//字体大小
        font1.setBold(false);//加粗
        font1.setFontName("宋体");
        style1.setFont(font1);//将字体样式放入style
        style1.setAlignment(HorizontalAlignment.CENTER);//左右居中
        style1.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
        style1.setWrapText(true);//自动换行
        style1.setBorderBottom(BorderStyle.THIN); //下边框
        style1.setBorderLeft(BorderStyle.THIN);//左边框
        style1.setBorderTop(BorderStyle.THIN);//上边框
        style1.setBorderRight(BorderStyle.THIN);//右边框


        HSSFCellStyle style2 = workbook.createCellStyle();//样式2 带绿色背景颜色的
        style2.setFont(font1);//将字体样式放入style
        style2.setAlignment(HorizontalAlignment.CENTER);//左右居中
        style2.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
        style2.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex());
        style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style2.setWrapText(true);//自动换行
        style2.setBorderBottom(BorderStyle.THIN); //下边框
        style2.setBorderLeft(BorderStyle.THIN);//左边框
        style2.setBorderTop(BorderStyle.THIN);//上边框
        style2.setBorderRight(BorderStyle.THIN);//右边框

        HSSFCellStyle style3 = workbook.createCellStyle();//样式3 带红色背景颜色的 倒数第二行 合计用
        style3.setFont(font1);//将字体样式放入style
        style3.setAlignment(HorizontalAlignment.CENTER);//左右居中
        style3.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
        style3.setFillForegroundColor(IndexedColors.RED.getIndex());
        style3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style3.setBorderBottom(BorderStyle.THIN); //下边框
        style3.setBorderLeft(BorderStyle.THIN);//左边框
        style3.setBorderTop(BorderStyle.THIN);//上边框
        style3.setBorderRight(BorderStyle.THIN);//右边框

        HSSFCellStyle style4 = workbook.createCellStyle();//样式4 带蓝色背景颜色的 最后一行平均用
        style4.setFont(font1);//将字体样式放入style
        style4.setAlignment(HorizontalAlignment.CENTER);//左右居中
        style4.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
        style4.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());
        style4.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style4.setBorderBottom(BorderStyle.THIN); //下边框
        style4.setBorderLeft(BorderStyle.THIN);//左边框
        style4.setBorderTop(BorderStyle.THIN);//上边框
        style4.setBorderRight(BorderStyle.THIN);//右边框
        //第二行
        HSSFRow row1 = sheet.createRow(1);
        for (int i = 0; i <str.length ; i++) {
            HSSFCell cell1 = row1.createCell(i);
            row1.setHeight((short) 900); // 设置行高
            cell1.setCellValue(new HSSFRichTextString(str[i]));
            if(i == 3 || i == 6 || i == 9 || i == 12 || i == 15 || i==18 || i==21){
                cell1.setCellStyle(style2);//将style放入cell
            }else {
                cell1.setCellStyle(style1);//将style放入cell
            }
        }
        int totalcll=0;
        int totalclls=0;
        int totalclln=0;
        int totalrlls=0;
        int totalrlln=0;
        int totalrll=0;
        Double totalkdj=0D;
        Double totalkdjs=0D;
        Double totalkdjn=0D;
        Double totalordercounts=0D;
        Double totalordercountn=0D;
        Double totalordercount=0D;
        int totalordersizes=0;
        int totalordersizen=0;
        int totalordersize=0;
        int totalgcs=0;
        int totalgcn=0;
        int totalgc=0;
        Double totalrzxf=0D;
        Double totalrzxfs=0D;
        Double totalrzxfn=0D;
        DecimalFormat df = new DecimalFormat("0.00");
        DecimalFormat df1 = new DecimalFormat("0");
        //创建内容所有行
        for (int i = 0; i <timeList.size() ; i++) {
            HSSFRow row= sheet.createRow(i+2);//创建行
            row.setHeight((short) 600); // 设置行高
            for (int j = 0; j <str.length ; j++) {
                HSSFCell cell = row.createCell(j);//创建单元格
                if(j ==0){//日期
                    cell.setCellValue(timeList.get(i));
                }else if(j ==1){//车流
                    totalclls +=clls.get(i);
                    cell.setCellValue(clls.get(i));
                }else if(j ==2){
                    totalclln +=clln.get(i);
                    cell.setCellValue(clln.get(i));
                }else if(j ==3){
                    totalcll +=((clls.get(i) + clln.get(i)));
                    cell.setCellValue((clls.get(i) + clln.get(i)));
                }else if(j ==4){//客流
                    totalrlls +=rlls.get(i);
                    cell.setCellValue(rlls.get(i));
                }else if(j ==5){
                    totalrlln +=rlln.get(i);
                    cell.setCellValue(rlln.get(i));
                }else if(j ==6){
                    totalrll +=(rlls.get(i)+rlln.get(i));
                    cell.setCellValue((rlls.get(i)+rlln.get(i)));
                }else if(j ==7){//厕位

                    totalgcs +=(maleSouth.get(i)+ femaleSouth.get(i));
                    cell.setCellValue( maleSouth.get(i)+femaleSouth.get(i));
                }else if(j ==8){
                    totalgcn +=(maleNorth.get(i)+femaleNorth.get(i));
                    cell.setCellValue(maleNorth.get(i)+femaleNorth.get(i));
                }else if(j ==9){
                    totalgc +=(maleSouth.get(i)+ femaleSouth.get(i)+maleNorth.get(i)+femaleNorth.get(i));
                    cell.setCellValue(maleSouth.get(i)+ femaleSouth.get(i)+maleNorth.get(i)+femaleNorth.get(i));
                }else if(j ==10){//销售额
                    totalordercounts +=(Double)sSaleCount.get(i);
                    cell.setCellValue(df.format(sSaleCount.get(i)));
                }else if(j ==11){
                    totalordercountn +=(Double)nSaleCount.get(i);
                    cell.setCellValue(df.format(nSaleCount.get(i)));
                }else if(j ==12){
                    Double ssale=(Double) sSaleCount.get(i);
                    Double nsale=(Double) nSaleCount.get(i);
                    Double tsale=(Double)(ssale+nsale);
                    totalordercount += (Double.parseDouble(df.format(tsale)));
                    cell.setCellValue(df.format(tsale));
                }else if(j ==13){//订单数量
                    totalordersizes +=(Integer) southOrderSize.get(i);
                    cell.setCellValue((Integer) southOrderSize.get(i));
                }else if(j ==14){
                    totalordersizen +=(Integer) northOrderSize.get(i);
                    cell.setCellValue((Integer) northOrderSize.get(i));
                }else if(j ==15){
                    totalordersize +=((Integer)southOrderSize.get(i)+(Integer)northOrderSize.get(i));
                    cell.setCellValue((Integer)southOrderSize.get(i)+(Integer)northOrderSize.get(i));
                }else if(j ==16){//客单价
                    totalkdjs +=southkdj.get(i);
                    cell.setCellValue(southkdj.get(i));
                }else if(j ==17){
                    totalkdjn +=northkdj.get(i);
                    cell.setCellValue(northkdj.get(i));
                }else if(j ==18){
                    Double sk=(Double)southkdj.get(i);
                    Double nk=(Double)northkdj.get(i);
                    Double tk=(Double)(sk+nk);
                    totalkdj +=((Double) tk/2);
                    cell.setCellValue(((Double) tk/2));
                }else if(j ==19){//人均消费占比
                    totalrzxfs +=(Double) (srjxf.get(i));
                    cell.setCellValue((Double) (srjxf.get(i)));//+"%"
                }else if(j ==20){
                    totalrzxfn +=(Double) nrjxf.get(i);
                    cell.setCellValue((Double) nrjxf.get(i));//+"%"
                }else if(j ==21){
                    Double d=((Double)srjxf.get(i)+(Double)nrjxf.get(i))/2;
                    totalrzxf +=(Double.parseDouble(df.format(d)));
                    cell.setCellValue(Double.parseDouble(df.format(d)));//+"%"
                }
                cell.setCellStyle(style1);
            }
        }
        //总计
        HSSFRow rowLast2= sheet.createRow(timeList.size()+2);//创建行
        rowLast2.setHeight((short) 600); // 设置行高
        for (int i = 0; i <str.length ; i++) {
            HSSFCell cell = rowLast2.createCell(i);//创建单元格
            if(i == 0){
                cell.setCellValue("合计");
            }else if(i == 1){
                cell.setCellValue(totalclls);
            }else if(i == 2){
                cell.setCellValue(totalclln);
            }else if(i == 3){
                cell.setCellValue(totalcll);
            }else if(i == 4){
                cell.setCellValue(totalrlls);
            }else if(i == 5){
                cell.setCellValue(totalrlln);
            }else if(i == 6){
                cell.setCellValue(totalrll);
            }else if(i == 7){
                cell.setCellValue(totalgcs);
            }else if(i == 8){
                cell.setCellValue(totalgcn);
            }else if(i == 9){
                cell.setCellValue(totalgc);
            }else if(i == 10){
                cell.setCellValue(df.format(totalordercounts));
            }else if(i == 11){
                cell.setCellValue(df.format(totalordercountn));
            }else if(i == 12){
                cell.setCellValue(df.format(totalordercount));
            }else if(i == 13){
                cell.setCellValue(totalordersizes);
            }else if(i == 14){
                cell.setCellValue(totalordersizen);
            }else if(i == 15){
                cell.setCellValue(totalordersize);
            }else if(i == 16){
                cell.setCellValue(df.format(totalkdjs));
            }else if(i == 17){
                cell.setCellValue(df.format(totalkdjn));
            }else if(i == 18){
                cell.setCellValue(df.format(totalkdj));
            }else if(i == 19){
                cell.setCellValue(df.format(totalrzxfs));
            }else if(i == 20){
                cell.setCellValue(df.format(totalrzxfn));
            }else if(i == 21){
                cell.setCellValue(df.format(totalrzxf));
            }
            cell.setCellStyle(style3);
        }
        //平均
        HSSFRow rowLast= sheet.createRow(timeList.size()+3);//创建行
        rowLast.setHeight((short) 600); // 设置行高
        int size=timeList.size();
        if(size ==0){
            size =1;
        }
        for (int i = 0; i <str.length ; i++){
            HSSFCell cell = rowLast.createCell(i);//创建单元格
            if(i == 0){
                cell.setCellValue("平均");
            }else if(i == 1){
                String kgratio = df1.format((float)totalclls/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(dzg);
            }else if(i == 2){
                String kgratio = df1.format((float)totalclln/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(dzg);
            }else if(i == 3){
                String kgratio = df1.format((float)totalcll/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(dzg);
            }else if(i == 4){
                String kgratio = df1.format((float)totalrlls/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(dzg);
            }else if(i == 5){
                String kgratio = df1.format((float)totalrlln/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(dzg);
            }else if(i == 6){
                String kgratio = df1.format((float)totalrll/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(dzg);
            }else if(i == 7){
                String kgratio = df1.format((float)totalgcs/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(dzg);
            }else if(i == 8){
                String kgratio = df1.format((float)totalgcn/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(dzg);
            }else if(i == 9){
                String kgratio = df1.format((float)totalgc/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(dzg);
            }else if(i == 10){
                String kgratio = df.format((Double) totalordercounts/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(df.format(dzg));
            }else if(i == 11){
                String kgratio = df.format((Double) totalordercountn/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(df.format(dzg));
            }else if(i == 12){
                String kgratio = df.format((Double) totalordercount/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(df.format(dzg));
            }else if(i == 13){
                String kgratio = df1.format((float) totalordersizes/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(dzg);
            }else if(i == 14){
                String kgratio = df1.format((float) totalordersizen/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(dzg);
            }else if(i == 15){
                String kgratio = df1.format((float) totalordersize/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(dzg);
            }else if(i == 16){
                String kgratio = df.format((Double) totalkdjs/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(df.format(dzg));
            }else if(i == 17){
                String kgratio = df.format((Double) totalkdjn/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(df.format(dzg));
            }else if(i == 18){
                String kgratio = df.format((Double) totalkdj/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(df.format(dzg));
            }else if(i == 19){
                String kgratio = df.format((Double) totalrzxfs/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(df.format(dzg));
            }else if(i == 20){
                String kgratio = df.format((Double) totalrzxfn/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(df.format(dzg));
            }else if(i == 21){
                String kgratio = df.format((Double) totalrzxf/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(df.format(dzg));
            }
            cell.setCellStyle(style4);
        }
        try {
            fileName = new String(fileName.getBytes(),"ISO8859-1");
            OutputStream output=response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename="+fileName);
            response.setContentType("application/msexcel");
            workbook.write(output);
            output.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * 列表
     */
    @RequestMapping("/list")
    @RequiresPermissions("cll:cllvehiclerecord:list")
    public R list(@RequestParam Map<String, Object> params){
        PageUtils page = cllVehicleRecordService.queryPage(params);

        return R.ok().put("page", page);
    }
    /**
     * 信息
     */
    @RequestMapping("/info/{id}")
    @RequiresPermissions("cll:cllvehiclerecord:info")
    public R info(@PathVariable("id") String id){
        CllVehicleRecordEntity cllVehicleRecord = cllVehicleRecordService.getById(id);

        return R.ok().put("cllVehicleRecord", cllVehicleRecord);
    }

    /**
     * 保存
     */
    @RequestMapping("/save")
    @RequiresPermissions("cll:cllvehiclerecord:save")
    public R save(@RequestBody CllVehicleRecordEntity cllVehicleRecord){
        cllVehicleRecordService.save(cllVehicleRecord);

        return R.ok();
    }

    /**
     * 修改
     */
    @RequestMapping("/update")
    @RequiresPermissions("cll:cllvehiclerecord:update")
    public R update(@RequestBody CllVehicleRecordEntity cllVehicleRecord){
        ValidatorUtils.validateEntity(cllVehicleRecord);
        cllVehicleRecordService.updateById(cllVehicleRecord);
        
        return R.ok();
    }

    /**
     * 删除
     */
    @RequestMapping("/delete")
    @RequiresPermissions("cll:cllvehiclerecord:delete")
    public R delete(@RequestBody String[] ids){
        cllVehicleRecordService.removeByIds(Arrays.asList(ids));

        return R.ok();
    }
    /**
     * 昨日、今日、本月车流
     */
    @RequestMapping("/cllYesterdayTodayThismonth")
    public R cllYesterdayTodayThismonth(@RequestParam Map<String, Object> params){
        String date=DateUtil.format(new Date(),"yyyy-MM-dd");
        //昨天
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(new Date());
        calendar.add(Calendar.DAY_OF_MONTH, -1);
        String  yesterday= DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
        String  yesterdayStartTime =yesterday+" 00:00:00";
        String  yesterdayendTime =yesterday+" 23:59:59";
        //今天
        String todayStartTime=date+" 00:00:00";
        String todayEndTime=date+" 23:59:59";
        //本月
        String datemonth= DateUtil.format(new Date(),"yyyy-MM");
        String thismothStartTime =datemonth+"-01 00:00:00";
        calendar.setTime(new Date());
        calendar.add(Calendar.MONTH, 1);
        calendar.set(Calendar.DAY_OF_MONTH, 0);
        String thismothEndTime = DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
        thismothEndTime +=" 23:59:59";
        return R.ok()
                .put("today",cllVehicleRecordService.cllYesterdayTodayThismonth(todayStartTime,todayEndTime))
                .put("yesterday",cllVehicleRecordService.cllYesterdayTodayThismonth(yesterdayStartTime,yesterdayendTime))
                .put("thismoth",cllVehicleRecordService.cllYesterdayTodayThismonth(thismothStartTime,thismothEndTime))
                ;
    }
    /**
     * 南北区 车流量统计 近七天 近三十天 自定义时间
     */
    @RequestMapping("/cllSouthNorthStatistics")
    public R cllSouthNorthStatistics(@RequestParam Map<String, Object> params){
        String southMonitoringId ="1bd4530bfe534770a11ea196b34663fc";//南区入口id
        String northMonitoringId ="095852b3f7c948d6b27d5fd698bd1c37";//北区入口id
        String type= (String) params.get("type");
        String stime= (String) params.get("startTime");
        String etime= (String) params.get("endTime");
        if("".equals(type) || "null".equals(type)){
            type="7";
        }
        Date dd=new Date();
        String date=DateUtil.format(dd,"yyyy-MM-dd");
        String startTime="";
        String endTime="";
        Calendar calendar = Calendar.getInstance();
        if(!"".equals(stime) &&  !"null".equals(stime) && !"".equals(etime) &&  !"null".equals(etime)){//自定义时间
            startTime =stime+" 00:00:00";
            endTime =etime+" 23:59:59";
        }else{//无自定义时间
            if("7".equals(type)){//近7天
                calendar.setTime(dd);
                calendar.add(Calendar.DAY_OF_MONTH, -6);
                startTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                startTime +=" 00:00:00";
                endTime =date+" 23:59:59";
            }else if("30".equals(type)){//近30天
                calendar.setTime(dd);
                calendar.add(Calendar.DAY_OF_MONTH, -29);
                startTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                startTime +=" 00:00:00";
                endTime =date+" 23:59:59";
            }
        }
        return R.ok()
                .put("south",cllVehicleRecordService.cllChartday(southMonitoringId,startTime,endTime))
                .put("north",cllVehicleRecordService.cllChartday(northMonitoringId,startTime,endTime));
    }
    /**
     * 近 七天 30天 自定义时间内的 车辆归属地查询
     */
    @RequestMapping("/cllOwnership")
    public R cllOwnership(@RequestParam Map<String, Object> params){
        String type= (String) params.get("type");
        String stime= (String) params.get("startTime");
        String etime= (String) params.get("endTime");
        Date dd=new Date();
        String date=DateUtil.format(dd,"yyyy-MM-dd");
        String startTime="";
        String endTime="";
        if("".equals(type) || "null".equals(type)){
            type="7";
        }
        Calendar calendar = Calendar.getInstance();
        if(!"".equals(stime) &&  !"null".equals(stime) && !"".equals(etime) &&  !"null".equals(etime)){//自定义时间
            startTime =stime+" 00:00:00";
            endTime =etime+" 23:59:59";
        }else{//无自定义时间
            if("7".equals(type)){//近7天
                calendar.setTime(dd);
                calendar.add(Calendar.DAY_OF_MONTH, -6);
                startTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                startTime +=" 00:00:00";
                endTime =date+" 23:59:59";
            }else if("30".equals(type)){//近30天
                calendar.setTime(dd);
                calendar.add(Calendar.DAY_OF_MONTH, -29);
                startTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                startTime +=" 00:00:00";
                endTime =date+" 23:59:59";
            }
        }
        return R.ok().put("carownership",cllVehicleRecordService.carownership(startTime,endTime));
    }
    /**
     * 逗留时长
     */
    @RequestMapping("/stayTime")
    public R stayTime(@RequestParam Map<String, Object> params){
        return R.ok().put("stayTime",cllVehicleRecordService.stayTime());
    }
    /**
     * 近 七天 30天 自定义时间内的 车辆类型统计
     */
    @RequestMapping("/cllCartype")
    public R cllCartype(@RequestParam Map<String, Object> params){
        String southMonitoringId ="1bd4530bfe534770a11ea196b34663fc";//南区入口id
        String northMonitoringId ="095852b3f7c948d6b27d5fd698bd1c37";//北区入口id
        String type= (String) params.get("type");
        String stime= (String) params.get("startTime");
        String etime= (String) params.get("endTime");
        Date dd=new Date();
        String date=DateUtil.format(dd,"yyyy-MM-dd");
        String startTime="";
        String endTime="";
        Calendar calendar = Calendar.getInstance();
        if("".equals(type) || "null".equals(type)){
            type="7";
        }
        if(!"".equals(stime) &&  !"null".equals(stime) && !"".equals(etime) &&  !"null".equals(etime)){//自定义时间
            startTime =stime+" 00:00:00";
            endTime =etime+" 23:59:59";
        }else{//无自定义时间
            if("7".equals(type)){//近7天
                calendar.setTime(dd);
                calendar.add(Calendar.DAY_OF_MONTH, -6);
                startTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                startTime +=" 00:00:00";
                endTime =date+" 23:59:59";
            }else if("30".equals(type)){//近30天
                calendar.setTime(dd);
                calendar.add(Calendar.DAY_OF_MONTH, -29);
                startTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                startTime +=" 00:00:00";
                endTime =date+" 23:59:59";
            }
        }
        return R.ok()
                .put("southcartype",cllVehicleRecordService.cllCartype(southMonitoringId,startTime,endTime))
                .put("northcartype",cllVehicleRecordService.cllCartype(northMonitoringId,startTime,endTime));
    }
    /**
     * 今日车流量
     */
    @RequestMapping("/cllCount")
    public R cllCount(@RequestParam Map<String, Object> params){
        String type= (String) params.get("type");
        String time= (String) params.get("time");
        String southMonitoringId ="1bd4530bfe534770a11ea196b34663fc";//南区入口id
        String northMonitoringId ="095852b3f7c948d6b27d5fd698bd1c37";//北区入口id
        String startTime="";
        String endTime="";
        String date=DateUtil.format(new Date(),"yyyy-MM-dd");
        if(type==null && time==null){
            startTime=date+" 00:00:00";
            endTime=date+" 23:59:59";
        }else if(!"".equals(time) && time !=null){//有自定义时间
            if("0".equals(type)){//该自定义时间 的当天的起止时间的数据
                startTime =time+" 00:00:00";
                endTime =time+" 23:59:59";
            }else if("1".equals(type)){//该自定义时间 的当月的起止时间的数据
                startTime =time+"-01 00:00:00";
                Date dd=DateUtil.parse(startTime);
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(dd);
                calendar.add(Calendar.MONTH, 1);
                calendar.set(Calendar.DAY_OF_MONTH, 0);
                endTime = DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                endTime +=" 23:59:59";
            }
        }else {//无自定义时间
            if("0".equals(type)){//当天的数据
                startTime=date+" 00:00:00";
                endTime=date+" 23:59:59";
            }else if("1".equals(type)){//当月的数据
                String d=DateUtil.format(new Date(),"yyyy-MM");
                startTime = d+"-01 00:00:00";
                // 获取当前月的最后一天
                Calendar calendar1 = Calendar.getInstance();
                calendar1.setTime(new Date());
                calendar1.add(Calendar.MONTH, 1);
                calendar1.set(Calendar.DAY_OF_MONTH, 0);
                endTime = DateUtil.format(calendar1.getTime(),"yyyy-MM-dd");
                endTime +=" 23:59:59";
            }
        }
        //南区
        Integer southCount=cllVehicleRecordService.cllCount(startTime,endTime,southMonitoringId);
        Integer northCount=cllVehicleRecordService.cllCount(startTime,endTime,northMonitoringId);
        return R.ok().put("cllCount",String.valueOf(southCount+northCount).toCharArray()).put("southCount",southCount).put("northCount",northCount);
    }
    /**
     * 24小时车流量趋势
     */
    @RequestMapping("/cllChart/{type}")
    public R cllChart(@PathVariable("type") String type){
        String southMonitoringId ="1bd4530bfe534770a11ea196b34663fc";//南区入口id
        String northMonitoringId ="095852b3f7c948d6b27d5fd698bd1c37";//北区入口id
        String startTime="";
        String endTime="";
        if("0".equals(type)){
            startTime= DateUtil.format(new Date(),"yyyy-MM-dd");
            startTime+=" 00:00:00";
            endTime=DateUtil.format(new Date(),"yyyy-MM-dd HH:mm:ss");
        }
        List southList=cllVehicleRecordService.cllChart(southMonitoringId,startTime,endTime);
        List northList=cllVehicleRecordService.cllChart(northMonitoringId,startTime,endTime);
        return R.ok().put("southList",southList).put("northList",northList);
    }
    /**
     * 车辆归属地
     */
    @RequestMapping("/carownership")
    public R carownership(@RequestParam Map<String, Object> params){
        Date date=new Date();
        String type= (String) params.get("type");
        String time= (String) params.get("time");
        String startTime="";
        String endTime="";
        if(!"".equals(time) && time !=null){//有自定义时间
            if("0".equals(type)){//该自定义时间 的当天的起止时间的数据
                startTime =time+" 00:00:00";
                endTime =time+" 23:59:59";
            }else if("1".equals(type)){//该自定义时间 的当月的起止时间的数据
                startTime =time+"-01 00:00:00";
                Date dd=DateUtil.parse(startTime);
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(dd);
                calendar.add(Calendar.MONTH, 1);
                calendar.set(Calendar.DAY_OF_MONTH, 0);
                endTime = DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                endTime +=" 23:59:59";
            }
        }else {//无自定义时间
            if("0".equals(type)){//当天的数据
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(date);
                calendar.add(Calendar.HOUR_OF_DAY, 1);//1小时后
                calendar.set(Calendar.MINUTE, 0);
                calendar.set(Calendar.SECOND, 0);
                calendar.set(Calendar.MILLISECOND, 0);
                endTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd HH:mm:ss");
                startTime=DateUtil.format(date,"yyyy-MM-dd");
                startTime +=" 00:00:00";
            }else if("1".equals(type)){//当月的数据
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(date);
                // 获取前月的第一天
                calendar.add(Calendar.MONTH, 0);
                calendar.set(Calendar.DAY_OF_MONTH, 1);
                startTime= DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                startTime +=" 00:00:00";
                // 获取当前月的最后一天
                Calendar calendar1 = Calendar.getInstance();
                calendar1.setTime(date);
                calendar1.add(Calendar.MONTH, 1);
                calendar1.set(Calendar.DAY_OF_MONTH, 0);
                endTime = DateUtil.format(calendar1.getTime(),"yyyy-MM-dd");
                endTime +=" 23:59:59";
            }
        }
       return R.ok().put("carownership",cllVehicleRecordService.carownership(startTime,endTime));
    }
    /**
     * 车流量统计
     */
    @RequestMapping("/carflow")
    public R carflow(@RequestParam Map<String, Object> params){
        String type= (String) params.get("type");
        String time= (String) params.get("time");
        Date date=new Date();
        String southMonitoringId ="1bd4530bfe534770a11ea196b34663fc";//南区入口id
        String northMonitoringId ="095852b3f7c948d6b27d5fd698bd1c37";//北区入口id

        String northoutMonitoringIid ="695673bb75514da79535e084956b3d76";//北区出口id
        String southoutMonitoringIid ="bdc59eaa734e4f2ab048bdaa5e55a6d5";//南区出口id
        String startTime="";
        String endTime="";
        if(!"".equals(time) && time !=null){//有自定义时间
            if("0".equals(type)){//该自定义时间 的当天的起止时间的数据
                startTime =time+" 00:00:00";
                endTime =time+" 23:59:59";
            }else if("1".equals(type)){//该自定义时间 的当月的起止时间的数据
                startTime =time+"-01 00:00:00";
                Date dd=DateUtil.parse(startTime);
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(dd);
                calendar.add(Calendar.MONTH, 1);
                calendar.set(Calendar.DAY_OF_MONTH, 0);
                endTime = DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                endTime +=" 23:59:59";
            }
        }else {//无自定义时间
            if("0".equals(type)){//当天的数据
                endTime=DateUtil.format(date,"yyyy-MM-dd HH:mm:ss");
                startTime=DateUtil.format(date,"yyyy-MM-dd");
                startTime +=" 00:00:00";
            }else if("1".equals(type)){//当月的数据
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(date);
                // 获取前月的第一天
                calendar.add(Calendar.MONTH, 0);
                calendar.set(Calendar.DAY_OF_MONTH, 1);
                startTime= DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                startTime +=" 00:00:00";
                endTime = DateUtil.format(date,"yyyy-MM-dd");
                endTime +=" 23:59:59";

            }
        }
        if("0".equals(type)){//小时
            return R.ok()
                    .put("southinlist",cllVehicleRecordService.cllChart(southMonitoringId,startTime,endTime))
                    .put("southoutlist",cllVehicleRecordService.cllChart(southoutMonitoringIid,startTime,endTime))
                    .put("northlist",cllVehicleRecordService.cllChart(northMonitoringId,startTime,endTime))
                    .put("northoutlist",cllVehicleRecordService.cllChart(northoutMonitoringIid,startTime,endTime));
        }else if("1".equals(type)){//天
            return R.ok()
                    .put("southinlist",cllVehicleRecordService.cllChartday(southMonitoringId,startTime,endTime))
                    .put("southoutlist",cllVehicleRecordService.cllChartday(southoutMonitoringIid,startTime,endTime))
                    .put("northlist",cllVehicleRecordService.cllChartday(northMonitoringId,startTime,endTime))
                    .put("northoutlist",cllVehicleRecordService.cllChartday(northoutMonitoringIid,startTime,endTime));
        }
        return R.ok();
    }
//    /**
//     * 车流量统计
//     */
//    @RequestMapping("/carflow")
//    public R carflow(@RequestParam Map<String, Object> params){
//        String type= (String) params.get("type");
//        String time= (String) params.get("time");
//        String southMonitoringIid ="bdd66d783a974b0a9920890e9958d503";//南区入口id
//        String northMonitoringIid ="8827c5b9737648e0911f25cc646034b5";//北区入口id
//        String northoutMonitoringIid ="023e9ef9081b46478bb1215949f04dfc";//北区出口id
//        String southoutMonitoringIid ="b20934b644394cf19c72f7b7d97c67aa";//南区出口id
//        return R.ok()
//                .put("southinlist",cllVehicleRecordService.carflow(type,time,southMonitoringIid))
//                .put("southoutlist",cllVehicleRecordService.carflow(type,time,southoutMonitoringIid))
//                .put("northlist",cllVehicleRecordService.carflow(type,time,northMonitoringIid))
//                .put("northoutlist",cllVehicleRecordService.carflow(type,time,northoutMonitoringIid));
//    }
    /**
     * 车辆进出查询
     */
    @RequestMapping("/carinout")
    public R carinout(){
        return R.ok().put("carinoutlist",cllVehicleRecordService.carinout());
    }
    /**
     * 车辆类型统计
     */
    @RequestMapping("/cartype")
    public R cartype(@RequestParam Map<String, Object> params){
        String type= (String) params.get("type");
        String time= (String) params.get("time");
        Date date=new Date();
        String startTime="";
        String endTime="";
        if(!"".equals(time) && time !=null){//有自定义时间
            if("0".equals(type)){//该自定义时间 的当天的起止时间的数据
                startTime =time+" 00:00:00";
                endTime =time+" 23:59:59";
            }else if("1".equals(type)){//该自定义时间 的当月的起止时间的数据
                startTime =time+"-01 00:00:00";
                Date dd=DateUtil.parse(startTime);
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(dd);
                calendar.add(Calendar.MONTH, 1);
                calendar.set(Calendar.DAY_OF_MONTH, 0);
                endTime = DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                endTime +=" 23:59:59";
            }
        }else {//无自定义时间
            if("0".equals(type)){//当天的数据
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(date);
                calendar.add(Calendar.HOUR_OF_DAY, 1);//1小时后
                calendar.set(Calendar.MINUTE, 0);
                calendar.set(Calendar.SECOND, 0);
                calendar.set(Calendar.MILLISECOND, 0);
                endTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd HH:mm:ss");
                startTime=DateUtil.format(date,"yyyy-MM-dd");
                startTime +=" 00:00:00";
            }else if("1".equals(type)){//当月的数据
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(date);
                // 获取前月的第一天
                calendar.add(Calendar.MONTH, 0);
                calendar.set(Calendar.DAY_OF_MONTH, 1);
                startTime= DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                startTime +=" 00:00:00";
                // 获取当前月的最后一天
                Calendar calendar1 = Calendar.getInstance();
                calendar1.setTime(date);
                calendar1.add(Calendar.MONTH, 1);
                calendar1.set(Calendar.DAY_OF_MONTH, 0);
                endTime = DateUtil.format(calendar1.getTime(),"yyyy-MM-dd");
                endTime +=" 23:59:59";

            }
        }

        return R.ok().put("cartypelist",cllVehicleRecordService.cartype(startTime,endTime));
    }

    @RequestMapping("/carPic")
    public void carPic(HttpServletRequest request,HttpServletResponse response)  {
        String carPic=request.getParameter("carPic");
        String aswSyscode=request.getParameter("aswSyscode");
        HttpResponse responseStr= HikvisionApiUtil.eventsImage(aswSyscode,carPic);
        HttpEntity en=responseStr.getEntity();
        ServletOutputStream out = null;
        InputStream in=null;

        try {
            in=en.getContent();
            response.setContentType("multipart/form-data");
            out = response.getOutputStream();
            //读取文件流
            int len = 0;
            byte[] buffer = new byte[1024 * 10];
            while ((len = in.read(buffer)) != -1){
                out.write(buffer,0,len);
            }
            out.flush();

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                out.close();
                in.close();
            }catch (IOException o){
                o.printStackTrace();
            }

        }
    }

    @RequestMapping("/platePic")
    public void platePic(HttpServletRequest request,HttpServletResponse response){
        String platePic=request.getParameter("platePic");
        String aswSyscode=request.getParameter("aswSyscode");
        HttpResponse responseStr= HikvisionApiUtil.eventsImage(aswSyscode,platePic);
        HttpEntity en=responseStr.getEntity();
        ServletOutputStream out = null;
        InputStream in=null;
        try {
            in=en.getContent();
            response.setContentType("multipart/form-data");
            out = response.getOutputStream();
            //读取文件流
            int len = 0;
            byte[] buffer = new byte[1024 * 10];
            while ((len = in.read(buffer)) != -1){
                out.write(buffer,0,len);
            }
            out.flush();

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                out.close();
                in.close();
            }catch (IOException o){
                o.printStackTrace();
            }

        }
    }
    /**
     * 导出
     */
   /* @RequestMapping("/exportcll")
    public void exportcll(@RequestParam Map<String, Object> params, HttpServletResponse response){
        String southMonitoringId ="bdd66d783a974b0a9920890e9958d503";//南区入口id
        String northMonitoringId ="8827c5b9737648e0911f25cc646034b5";//北区入口id
        String type= (String) params.get("type");
        String stime= (String) params.get("startTime");
        String etime= (String) params.get("endTime");
        Date dd=new Date();
        String date=DateUtil.format(dd,"yyyy-MM-dd");
        String startTime="";
        String endTime="";
        if("".equals(type) || "null".equals(type)){
            type="7";
        }
        Calendar calendar = Calendar.getInstance();
        if(!"".equals(stime) &&  !"null".equals(stime) && !"".equals(etime) &&  !"null".equals(etime)){//自定义时间
            startTime =stime+" 00:00:00";
            endTime =etime+" 23:59:59";
        }else{//无自定义时间
            if("7".equals(type)){//近7天
                calendar.setTime(dd);
                calendar.add(Calendar.DAY_OF_MONTH, -6);
                startTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                startTime +=" 00:00:00";
                endTime =date+" 23:59:59";
            }else if("30".equals(type)){//近30天
                calendar.setTime(dd);
                calendar.add(Calendar.DAY_OF_MONTH, -29);
                startTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                startTime +=" 00:00:00";
                endTime =date+" 23:59:59";
            }
        }
        List<JxcShopEntity> shopList=jxcShopService.list();
        List<JxcShopEntity> southShopList=new ArrayList<>();
        List<JxcShopEntity> northShopList=new ArrayList<>();
        for (JxcShopEntity shop:shopList) {
            if(shop.getDeptId().equals("3")){//南区
                southShopList.add(shop);
            }else if(shop.getDeptId().equals("4")){
                northShopList.add(shop);
            }
        }
        List sclllist= cllVehicleRecordService.cllChartday(southMonitoringId,startTime,endTime);//南区车流量
        List nclllist=cllVehicleRecordService.cllChartday(northMonitoringId,startTime,endTime);//北区车流量

        List srlllist=rllHourCountService.kllChartday("南区",startTime,endTime);//南区客流量 list.get(1)
        List nrlllist=rllHourCountService.kllChartday("北区",startTime,endTime);//北区客流量

        List skdj=jxcSaleOrderService.southAndNorthAveragePriceDay(southShopList,startTime,endTime);//南区客单价 list.get(1)
        List nkdj=jxcSaleOrderService.southAndNorthAveragePriceDay(northShopList,startTime,endTime);//北区客单价

        List srjxf=jxcSaleOrderService.proportionOfConsumers("3",startTime,endTime);//南区人均消费占比
        List nrjxf=jxcSaleOrderService.proportionOfConsumers("4",startTime,endTime);//北区人均消费占比

        List southOrderSize=jxcSaleOrderService.orderSizelist(southShopList,startTime,endTime );//南区订单数
        List northOrderSize=jxcSaleOrderService.orderSizelist(northShopList,startTime,endTime); //北区订单数

        List sSaleCount= jxcSaleOrderService.orderCountlist(southShopList,startTime,endTime);//南区销售额
        List nSaleCount=jxcSaleOrderService.orderCountlist(northShopList,startTime,endTime);//北区销售额

        //创建excel导出
        String[] title = {"盂县服务区数据统计"};
        // excel文件名
        String fileName = "车流量统计"+new Date().getTime()+".xls";
        // sheet名
        String sheetName = "车流量统计";
        List<String> timeList = (List<String>)sclllist.get(0);
        List<Integer> clls = (List<Integer>)sclllist.get(1);
        List<Integer> clln = (List<Integer>)nclllist.get(1);
        List<Integer> rlls= (List<Integer>) srlllist.get(1);
        List<Integer> rlln= (List<Integer>) nrlllist.get(1);
        List<Double> southkdj= (List<Double>) skdj.get(1);
        List<Double> northkdj= (List<Double>) nkdj.get(1);

        String [][] content = new String[timeList.size()+4][19];
        content[0][0]="日期";
        content[0][1]="南区车流";
        content[0][2]="北区车流";
        content[0][3]="合计";
        content[0][4]="南区客流";
        content[0][5]="北区客流";
        content[0][6]="合计";
        content[0][7]="南区销售额";
        content[0][8]="北区销售额";
        content[0][9]="总销售额";
        content[0][10]="南区订单量";
        content[0][11]="北区订单量";
        content[0][12]="总订单数量";
        content[0][13]="南区客单价";
        content[0][14]="北区客单价";
        content[0][15]="总客单价";
        content[0][16]="南区消费人次占比";
        content[0][17]="北区消费人次占比";
        content[0][18]="两区消费人次占比";
        DecimalFormat df = new DecimalFormat("0.00");
        for(int i=0;i<timeList.size();i++){
            content[i+1][0] = timeList.get(i);
            content[i+1][1] = String.valueOf(clls.get(i));
            content[i+1][2] = String.valueOf(clln.get(i));
            content[i+1][3] = String.valueOf((clls.get(i) + clln.get(i)));
            content[i+1][4] = String.valueOf(rlls.get(i));
            content[i+1][5] = String.valueOf(rlln.get(i));
            content[i+1][6] = String.valueOf((rlls.get(i)+rlln.get(i)));
            content[i+1][7] = String.valueOf(sSaleCount.get(i));
            content[i+1][8] = String.valueOf(nSaleCount.get(i));
            Double ssale=(Double) sSaleCount.get(i);
            Double nsale=(Double) nSaleCount.get(i);
            Double tsale=(Double)(ssale+nsale);
            content[i+1][9] = String.valueOf(tsale);
            content[i+1][10] = String.valueOf(southOrderSize.get(i));
            content[i+1][11] = String.valueOf(northOrderSize.get(i));
            content[i+1][12] = String.valueOf((Integer)southOrderSize.get(i)+(Integer)northOrderSize.get(i));
            content[i+1][13] = String.valueOf(southkdj.get(i));
            content[i+1][14] = String.valueOf(northkdj.get(i));
            Double sk=(Double)southkdj.get(i);
            Double nk=(Double)northkdj.get(i);
            Double tk=(Double)(sk+nk);
            content[i+1][15] = df.format((Double) tk/2);
            content[i+1][16] = srjxf.get(i)+"%";
            content[i+1][17] = nrjxf.get(i)+"%";
            content[i+1][18] =(df.format(((Double)(srjxf.get(i))+(Double)(nrjxf.get(i)))/2))+"%";
        }
        // 创建HSSFWorkbook
        HSSFWorkbook wb = POIExcelUtils.getHSSFWorkbook(sheetName, title,content, null);
        // 响应到客户端
        try {
            fileName = new String(fileName.getBytes(),"ISO8859-1");
            OutputStream output=response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename="+fileName);
            response.setContentType("application/msexcel");
            wb.write(output);
            output.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }*/
}
